Release 10.1A: OpenEdge Data Management:
DataServer for Microsoft SQL Server

Code pages

Flexibility in managing code pages, also called character sets, is critical for international applications. When you access an MSS data source through the DataServer, the DataServer retrieves character data as determined by the code page that the data source uses. For OpenEdge databases, the default code page is iso8859–1; however, the OpenEdge model allows you to designate code pages for the data source and for the client.

Figure 2–1 shows a possible configuration of code pages for the DataServer components and processes.

Figure 2–1: DataServer processes and code pages

In the configuration shown in Figure 2–1, all components use the same code page. The OpenEdge iso8859–1 code page is a subset of the iso_1 code page used by the default installation of MS SQL Server. For information on setting character sets for your data source, see the administration guide supplied by the vendor. On the OpenEdge side, if the client and the schema holder use different code pages, a conversion takes place between them.

In order for DataServer applications to manipulate data from an MSS data source accurately, you must specify the correct code page and collation in the schema holder. For OpenEdge applications accessing the DataServer, the schema holder identifies the code page of the character data. The DataServer sends the data source name for the code page to the data source to indicate the character set for the data that the data source returns.

Be sure to set the code page in the schema holder to match a code page that the MSS data source supports. To minimize the number of translations, specify the default code page that the data source uses. If OpenEdge does not support the code page, you can specify instead a compatible code page that is available for your data source. The directory %DLC%\prolang\convmap contains conversion tables for all of the code pages that OpenEdge supports. Check to see whether any of them match your code page.

The default code page setting in the schema holder is iso8859–1. You can specify a different code page for the schema holder at the following times:

When you create the DataServer schema for the MSS data source.
When you load a new schema with a specified code page into an existing schema holder. In this case, the newly loaded schema’s code page overrides the schema holder’s original code page.
Note: It is possible to change the code page at other times. However, because changing the code page does not affect the data already in the database, writing new data to your database using a different code page can corrupt the data in your database. You cannot use the PROUTIL utility to change the code page used by the DataServer.

Keep in mind that your MSS software configuration might have local requirements for defining the proper language interface between the ODBC drivers and the data source. See your Microsoft SQL Server database documentation for details.

Client code page

The Internal Code Page (-cpinternal) startup parameter determines the code page that the OpenEdge client uses when it manipulates data in memory. If the OpenEdge client uses a different code page from the code page set in the schema holder, the DataServer translates between the two code pages, so you must verify that the convmap.cp file contains a conversion table for the client and the code page setting in the schema holder. Suppose, for example, that you set the schema holder to code page ibm850 and the client uses code page iso8859–1. The convmap.cp file must include a table that converts from ibm850 to iso8859–1 and from iso8859–1 to ibm850. If convmap.cp does not include the appropriate table, you can define your own conversion table.

OpenEdge also allows you to define your own collation tables; however, customized collation tables only take effect after data source collation when you use the DataServer to access an MSS data source. The data source collation tables, not the OpenEdge collation tables, have first priority when you perform comparisons and sorts. After performing comparisons and sorts, the OpenEdge client may sort out records that do not conform to the requirements of your customized collation tables.

For example, if you use the default code page 1252 and the default case insensitive sort ordering with an MS SQL Server data source, collation of the tilde character (~), which is ASCII character 126, sorts before all the alphanumeric characters (0–9, a–z). If you were to select data from a character column name with the following:

select * from table where name <=CHR(126) (the equivalent of ~)

MS SQL Server returns records with columns that begin with a tilde character but no alphanumeric records, since ASCII character 126 is sorted below alphanumeric characters in this MS SQL Server collation.

Normally, the default OpenEdge collation sorts a tilde character above all alphanumeric characters. Therefore, in order for the above example to exhibit OpenEdge-like behavior and return alphanumeric records as well as records beginning with the tilde, the MS SQL Server sort order for this code page would need to be modified accordingly.

Conversely, if you execute the opposite:

select * from table where name >=CHR(126)

MS SQL Server returns records with columns that begin with a tilde character followed by all that begin with alphanumeric characters. However, the default OpenEdge collation, which sorts the tilde higher than all the alphanumeric characters, would omit records beginning with alphanumeric characters and only return records beginning with the tilde character.

To get the full result set returned from MS SQL Server from the OpenEdge client would require modifying the collation table associated with the OpenEdge code page and weighting it to match the MS SQL Server sort order.

For a complete discussion of how OpenEdge handles code-page issues, see OpenEdge Development: Internationalizing Applications .

Code page, collation, and case conflicts

How you specify code page, case sensitivity, and shadow column usage can lead to conflict between the attributes. It is best to establish the settings on these attributes prior to schema creation, and allow the schema pull to manage the integration into the schema image. Table 2–3 describes the behavior of the combinations.

Table 2–3: Case sensitivity interactions

Code Page1	Shadow Columns	Case Sensitive Field Attribute	Result
CS	Yes	Off	Shadow column provides case insensitivity.
CS	No	Off	UPPER() function provides case insensitivity.2
CS	Yes	On	Code page provides case sensitivity.
CS	No	On	Code page provides case sensitivity.
CI	Yes	Off3 (Is case-insensitive)	Shadow column provides case insensitivity.4
CI	No	Off (Is case-insensitive)	Code page provides case insensitivity.
CI	Yes	On	Code page provides case insensitivity.5
CI	No	On	Code page provides case insensitivity.6

1MSS code page is either Case Sensitive (CS) or Case Insensitive (CI). 2Be aware that relying on the UPPER() function can have a negative performance impact. 3This combination is not recommended. Don’t use shadow columns if the MSS code page can handle case-insensitivity instead. 4This combination is not recommended.Use a code page to support case insensitivity by identifying the code page as case insensitive on a schema pull. Fields will be marked case insensitive on the pull. 5Shadow columns are present, but play no role. 6Case sensitivity cannot be supported by OpenEdge. The client will throw out any results not returned in case insensitive order.